
***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


**********************************************************************
***EXECTIME GRID & ISIN_ID &  MATURITY CATEGORIES & PAYMENT DAYS
**********************************************************************

*** isin_id
egen isin_id= group(isin)

*** exec_time_grid (hourly time grid: 8am means 8-9 am)
gen double exec_time_grid = 30*2 *60000 * floor(exec_time/ ( 30*2*60 * 1000))
format exec_time_grid %tc 


**********************************************************************
********* BORN IN SAMPLE AND I_NEW
**********************************************************************

*Born in sample indicator 
gen a0 = (AuctionDate ==first_auction) if market_type=="auction" 
bys isin: egen born = max(a0)
replace born=0 if born==.  // never at auction as first auction means not born in sample
drop a0
label var born "indicator assumes 1 if isin is born in the sample"


*I_new self created indicator 
drop I_new // drop Jason's I_new variable 
gen a0 = (AuctionDate ==first_auction) if market_type=="auction" 
bys isin date: egen I_new = max(a0)
replace I_new=0 if I_new==.
drop a0 
label var I_new "indicator assumes  1 if the isin is first issued on that day"

	
**********************************************************************
*** DEALER DUMMIES 
**********************************************************************

*** PRIMARY DEALERS 
destring LEI, gen(E) force // JASON: had to put force here b/c numeric. 


gen c_is_d=0 
replace c_is_d=1 if inlist(E, 18, 24, 26,  46, 61, 67, 71, 77, 83, 88, 93, 94, 18001, 24001, 46001, 190, 191) 
gen b_is_d=0 
replace b_is_d=1  if inlist(bidderid, 18, 24, 26, 46, 61, 67, 71, 77, 83, 88, 93, 94,  18001, 24001, 46001, 190, 191)					

	*On the webside there are 19 PDs + government distributers for bills/bonds. 
	*But:  Sherbrooke st doesn’t exist in our sample.  It might be new. And beacon hasn’t traded a bond in over 5 years. 
	

************************************************
*** CLIENT TYPES
************************************************


*** CLEANING 
*auction data should not have a type											 
replace client_type_bond_bill_ddgs="" if market_type=="auction"		
replace client_type_MSR           ="" if market_type=="auction"	

*check if all types are unique to a lei	
	/*
	preserve
	keep if market_type=="secondary" 
	collapse IIROC_QUANTITY, by(LEI client_type_bond_bill_ddgs)
	bys LEI: gen check = _N
	browse if check>1
	restore

	preserve
	keep if market_type=="secondary" 
	collapse IIROC_QUANTITY, by(LEI client_type_MSR)
	bys LEI: gen check = _N
	browse if check>1
	restore
	*/

	*check 
	*tab client_type_MSR if LEI=="anonymous"
	*tab client_type_bond_bill_ddgs if LEI=="anonymous"
		*---> should be empty

*clean 
replace client_type_bond_bill_ddgs="Bank" if client_type_MSR=="Bank"
replace client_type_bond_bill_ddgs="Bank" if client_type_MSR=="Custodian bank" 
replace client_type_bond_bill_ddgs="Bank" if client_type_MSR=="Bank"
replace client_type_bond_bill_ddgs="Bank" if client_type_MSR=="Mortgage financing company" 
replace client_type_bond_bill_ddgs="Public entity" if client_type_MSR =="Central bank"
replace client_type_bond_bill_ddgs="Public entity" if client_type_MSR =="Municipality"
replace client_type_bond_bill_ddgs="Public entity" if LEI=="17" // bank of canada trading mostly for the government 
replace client_type_MSR			  ="Central bank"  if LEI=="17" // bank of canada trading mostly for the government 


*** AGGREGATE COUTERPARTY TYPES
*banks
gen c_type  	= ""						if market_type=="secondary"

replace c_type 	= "bank" 					if client_type_bond_bill_ddgs=="Bank" ///
											| client_type_bond_bill_ddgs=="Bank (other)" ///
											| client_type_bond_bill_ddgs=="Credit union" ///
											| client_type_bond_bill_ddgs=="Trust"  


replace c_type  = "other irroc member" 		if client_type_bond_bill_ddgs=="IIROC (dealer)" ///
											| client_type_bond_bill_ddgs =="Dealer (other)"

										   
replace c_type 	= "dealer" 					if client_type_bond_bill_ddgs=="Bank (auction dealer)" ///
											| client_type_bond_bill_ddgs=="Bank (bond auction dealer)" ///
											| client_type_bond_bill_ddgs=="GSD" 
											

replace c_type  ="IDB broker" 			    if client_type_bond_bill_ddgs=="IDB" 
replace c_type  ="other broker" 			if client_type_bond_bill_ddgs=="Brokerage" 
replace c_type  ="boc" 						if client_type_bond_bill_ddgs=="Bank of Canada"
replace c_type  ="asset manager" 			if client_type_bond_bill_ddgs=="Asset manager" ///
											| client_type_bond_bill_ddgs=="Investment fund"
replace c_type  ="pension" 					if client_type_bond_bill_ddgs=="Pension" 
replace c_type  ="insurance" 				if client_type_bond_bill_ddgs=="Insurance"
replace c_type  ="public entity" 			if client_type_bond_bill_ddgs=="Public entity" | client_type_MSR=="IIROC"

												
replace c_type ="other"       			    if  client_type_bond_bill_ddgs=="transfer agency" /// *transfer agency is a data repository company
											| client_type_bond_bill_ddgs=="Alternative Trading System operator"  ///  *this is the all-to-all platform (on all-to-all, called perimeter, trades are annonymous. you only see whether the counterparty is retail or institutional)
											| client_type_MSR=="Exchange" /// *TMS owns CDCCD 
											| client_type_bond_bill_ddgs=="CDS clearing" ///
											| client_type_MSR=="Central counterparty" ///
											| client_type_MSR == "Dealer-2-Dealer platform" 
												
															
replace c_type ="non-financial company"     if client_type_MSR =="Biotech company " ///
											| client_type_MSR =="Energy company" ///
											| client_type_MSR =="Food processing company"  ///
											| client_type_MSR =="Manufacturing company"  ///
											| client_type_MSR =="Mining company"  ///
											| client_type_MSR =="Retail company"  ///
											| client_type_MSR =="Technology company"  ///
											| client_type_MSR =="Utility company"  ///
											| client_type_MSR =="Transportation" ///
											| client_type_MSR =="Charity" ///
											| client_type_MSR =="Real Estate"

*check if the numbers coincide:	
count if c_type!=""
count if client_type_MSR!=""
	

**********************************************************************
*** COUNTERPARTY TYPE
**********************************************************************

*** COUNTERPARTY OF THE DATA
gen counterparty 		= "CLIENT" 		if counterpartyType==1
replace counterparty 	= "NONCLIENT" 	if counterpartyType==7
replace counterparty 	= "IDBB" 		if counterpartyType==4 
										* now are the three IDBBs and 3 dealer-to-dealer platforms (act as brokers):
										*Fdelity, Brokertec and a mystery “BTAM”, all carry client_type_MSR="Dealer-2-Dealer platform"
replace counterparty 	= "DEALER" 		if counterpartyType==2
replace counterparty 	= "BANK" 		if counterpartyType==3
replace counterparty 	= "ATS" 		if counterpartyType==5 

	*info
	*counterpartyType 
	*= 1 if CLIENT 
	*= 2 if DEALER
	*= 3 if Bank
	*= 4 if IDBB
	*= 7 if NON-CLIENT
	*= 5 if ATS
	
	
*** SELF CREATED COUNTER_TYPE 
gen c_is_mtrs_cash =0 
replace c_is_mtrs_cash = 1 if inlist(LEI, "24", "18", "94", "88", "83", "77") & market_type=="secondary" 
replace c_is_mtrs_cash = 1 if inlist(LEI, "71", "67",  "61", "46", "93") & market_type=="secondary" 
replace c_is_mtrs_cash = 1 if inlist(LEI, "31", "26")& market_type=="secondary" 
replace c_is_mtrs_cash = 1 if inlist(LEI, "18001", "24001", "46001", "190", "191")  & market_type=="secondary" 
replace c_is_mtrs_cash = 1 if inlist(LEI, "96") & market_type=="secondary" 

gen c_is_mtrs_repo =0 
replace c_is_mtrs_repo = 1 if inlist(LEI, "24", "18", "94", "88", "83", "77", "67", "61") & market_type=="secondary" 
replace c_is_mtrs_repo = 1 if inlist(LEI, "46", "93", "31", "46001", "24001", "18001", "191") & market_type=="secondary" 
replace c_is_mtrs_repo = 1 if inlist(LEI, "96")  & market_type=="secondary" 

	
gen counter_type 	 ="CLIENT" 			    	if market_type=="secondary"
replace counter_type ="NONCLIENT" 				if market_type=="secondary" & counterparty=="NONCLIENT"
replace counter_type ="DEALER" 			        if (c_is_d==1 |c_is_mtrs_cash==1 | c_is_mtrs_repo==1 ) & market_type=="secondary"
replace counter_type ="BROKER" 			        if (LEI=="194" | LEI=="196" | LEI =="200") & market_type=="secondary"
replace counter_type ="ATS or other central party" 	if (counterpartyType==5  | c_type=="other") & market_type=="secondary"


************************************************
**** TRACKCLIENT INDICATOR
************************************************

** VERSION 0 
gen trackclient = "market-wide"			 if track_all==1
replace trackclient = "within dealer" 	 if track_all==0 & LEI!="anonymous"
replace trackclient = "no"				 if track_all==0 & LEI=="anonymous"


** VERSION 1 (relies on identified clients with a type)
gen trackclient2     = "missing" 			if market_type=="secondary"
replace trackclient2 = "market-wide" 		if client_type_MSR!="" 
replace trackclient2 = "within dealer" 		if client_type_MSR=="" & (LEI!="anonymous")
replace trackclient2 = "no" 				if client_type_MSR=="" & (LEI=="anonymous")


** VERSION 2 (relies on LEI_flags)
tempfile tempbase
save  `tempbase' , replace

keep if market_type=="secondary"
collapse IIROC_QUANTITY, by(LEI flag_LEI_is_customerLEI flag_LEI_is_customerID c_type market_type)

gen flag_customerLEI = (flag_LEI_is_customerID==0 & flag_LEI_is_customerLEI==0)
gen flag_LEI 		 = (flag_customerLEI==1) | (flag_LEI_is_customerLEI==1)

bys LEI: gen a0=_N
browse LEI flag_LEI c_type if a0>1

bys LEI: egen a1 = max(flag_LEI)
replace flag_LEI=a1 				if LEI!="anonymous"

drop if LEI=="anonymous"

collapse flag_LEI, by(LEI  c_type market_type)
tab flag_LEI // should be all 0 or 1

tempfile tempadd
save  `tempadd' , replace


use `tempbase' , replace 
merge m:1 LEI market_type using `tempadd' 

tab LEI if _merge==1 // should be all anonymous or auctions
drop _merge


*Version 1 of tarckclient 
gen trackclient3 ="no" 		  		  if  market_type=="secondary"
replace trackclient3 ="market-wide"   if flag_LEI==1 & market_type=="secondary"
replace trackclient3 ="within dealer" if flag_LEI==0 & market_type=="secondary"


	/*
	Info how flag_LEI's were created:
	
	gen flag_LEI_is_customerLEI = (counterpartyLEI=="" & customerLEI!="")
	replace counterpartyLEI=customerLEI if counterpartyLEI==""
	rename counterpartyLEI LEI
	drop customerLEI
	gen flag_LEI_is_customerID = (LEI=="" & customerAccountID!="")
	*/


************************************************
**** INSTITUIONAL VS RETAILER
************************************************

gen IIROC_CUST_ACC_TYPE_CODE 	 = "INSTITUTIONAL" 	if customerAccountType==1
replace IIROC_CUST_ACC_TYPE_CODE = "RETAIL" 		if customerAccountType==2 
replace IIROC_CUST_ACC_TYPE_CODE = "NOVALUE" 		if customerAccountType==3

count if (IIROC_CUST_ACC_TYPE_CODE!="INSTITUTIONAL" & IIROC_CUST_ACC_TYPE_CODE!="NOVALUE") &  (LEI=="194" | LEI=="196" | LEI=="200")  & market_type=="secondary"
count if (IIROC_CUST_ACC_TYPE_CODE!="INSTITUTIONAL" & IIROC_CUST_ACC_TYPE_CODE!="NOVALUE") &  (c_is_d==1 |c_is_mtrs_cash==1 | c_is_mtrs_repo==1 )  & market_type=="secondary"

*clean for the IDB brokers
replace IIROC_CUST_ACC_TYPE_CODE = "INSTITUTIONAL" 		if (LEI=="194" | LEI=="196" | LEI=="200")  & market_type=="secondary"
replace counterpartyType=4 								if (LEI=="194" | LEI=="196" | LEI=="200")  & market_type=="secondary"

*clean for all reporting dealers: 
replace IIROC_CUST_ACC_TYPE_CODE = "INSTITUTIONAL" 		if (c_is_d==1 |c_is_mtrs_cash==1 | c_is_mtrs_repo==1 )  & market_type=="secondary"
replace counterpartyType=2								if (c_is_d==1 |c_is_mtrs_cash==1 | c_is_mtrs_repo==1 )  & market_type=="secondary"

*retailer dummy 
gen retailer =.
replace retailer = 0  if IIROC_CUST_ACC_TYPE_CODE=="INSTITUTIONAL"
replace retailer = 1  if IIROC_CUST_ACC_TYPE_CODE=="RETAIL" 

label define retailertyoes 0 "institutional" 1 "retailer" 
label values retailer retailertyoes  


tempfile temp
save  `temp' , replace

	keep if market_type=="secondary"
	keep if LEI!="anonymous"
	gen freq =1
	collapse (sum) freq, by(LEI retailer)
	bys LEI: gen a0 =_N

	browse LEI retailer freq if a0>1
	gen missing = missing(retailer)
	bys LEI: egen a1=max(missing)
	keep if a1==1
	browse 
	bys LEI: gen a2 = _N
	browse if a2==2
	keep if a2==2
	keep if retailer!=.
	keep LEI retailer
	
tempfile tempadd
save  `tempadd' , replace

use  `temp' , clear
merge m:1 LEI using  `tempadd' , replace update 
drop _merge


*Cleaning: retail investors have no access to CanDeal
drop if retailer==1 & trading_venue=="CanDeal"

 
************************************************
**** Dummy for which LEI is in which market segment (bonds vs. bills)
************************************************

tempfile templei0
save  `templei0' , replace 
 
keep if trackclient =="market-wide" & LEI!="anonymous"
collapse IIROC_QUANTITY, by(LEI c_type type)

bys LEI: gen a0=_N
bys LEI type: gen a1=_N

	count if a1>1

	tab a0 type
	*--->  212  only in bonds,  334 only in bills,    469  in both 

	*client types who do only bonds 
	tab c_type if a0==1 & type=="bills"

	*client types who do only bills 
	tab c_type if a0==1 & type=="bonds"
 
	*client types who do both
	tab c_type if a0==2 & (type=="bills" | type=="bonds")
	*---> types don't look too different
	
	
gen segment 		= 0
replace segment 	= 1 if a0==1 & type=="bills"
replace segment 	= 2 if a0==1 & type=="bonds"
replace segment 	= 3 if a0==2 & (type=="bills" | type=="bonds")

drop a0 a1 

label define segmentlabels 1 "only bills" 2 "only bonds" 3 "bills and bonds" 
label values segment segmentlabels  


tempfile templei1
save  `templei1' , replace 

use  `templei0' , clear
merge m:m LEI c_type type using `templei1'
drop _merge



